package org.eevolution.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.model.Mhrmorder;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class MHRContract extends X_HR_Contract {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 458745728578291699L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (MHRContract.class);

	public MHRContract(Properties ctx, int HR_Contract_ID, String trxName) {
		super(ctx, HR_Contract_ID, trxName);
	}

	public MHRContract(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}

	public static X_HR_Contract[] getContracts(int block_id, int department_id){
		
		X_HR_Contract values = null;
		List<X_HR_Contract> list = new ArrayList<X_HR_Contract>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;	
		
		StringBuffer sql = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		
		sql.append("SELECT * FROM HR_CONTRACT \n"+
				"JOIN C_BPARTNER ON C_BPARTNER.C_BPARTNER_ID=HR_CONTRACT.C_BPARTNER_ID AND C_BPARTNER.ISACTIVE='Y' \n" +
				"JOIN HR_EMPLOYEE ON HR_EMPLOYEE.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID " +
				"               AND HR_EMPLOYEE.STARTDATE = (SELECT MAX(H.STARTDATE) FROM HR_EMPLOYEE H WHERE H.C_BPARTNER_ID=HR_EMPLOYEE.C_BPARTNER_ID) \n" +
				"LEFT JOIN AD_TREENODEDEP ON AD_TREENODEDEP.NODE_ID=HR_EMPLOYEE.HR_DEPARTMENT_ID \n" +
				"JOIN HR_JOB ON HR_JOB.HR_JOB_ID=HR_EMPLOYEE.HR_JOB_ID \n");
		
		if (department_id != 0){
			sql.append(" AND HR_EMPLOYEE.HR_DEPARTMENT_ID=? \n");
			param.add(department_id);
		}
		else if(block_id !=0){
			sql.append(" AND HR_EMPLOYEE.HR_DEPARTMENT_ID IN (SELECT NODE_ID FROM AD_TREENODEDEP WHERE PARENT_ID=?) \n");
			param.add(block_id);
		}
		
		sql.append("ORDER BY AD_TREENODEDEP.PARENT_ID ASC, AD_TREENODEDEP.SEQNO, HR_JOB.C_JOBCATEGORY_ID, HR_EMPLOYEE.C_BPARTNER_ID");

		try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);
			DB.setParameters(pstmt, param);
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new X_HR_Contract(Env.getCtx(), rs, null);
				list.add(values);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new X_HR_Contract[list.size()]);
	}

}
